package com.itmg.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.springframework.orm.ibatis.support.SqlMapClientDaoSupport;

import com.itmg.dao.DataDAO;
import com.itmg.dao.model.DataModel;
import com.itmg.vo.SearchResultItemVO;
public class DataDAOImpl extends SqlMapClientDaoSupport implements DataDAO {
	
	//插入到数据库中
	public void saveData(List<DataModel> list) throws Exception{
		 Connection con = null;
		 String sql ="insert into shop_data (" +
			 		"product_name," +
			 		"product_id," +
			 		"comment," +
			 		"product_url," +
			 		"maker_name," +
			 		"small_image_url," +
			 		"big_image_url," +
			 		"lowest_price," +
			 		"max_price," +
			 		"my_price," +
			 		"shop_name," +
			 		"shop_url," +
			 		"source_site_name," +
			 		"count," +
			 		"now_price," +
			 		"page_total," +
			 		"total," +
			 		"evaluation," +
			 		"create_time) values (" +
			 		"?," +
			 		"?," +
			 		"?," +
			 		"?," +
			 		"?," +
			 		"?," +
			 		"?," +
			 		"?," +
			 		"?," +
			 		"?," +
			 		"?," +
			 		"?," +
			 		"?," +
			 		"?," +
			 		"?," +
			 		"?," +
			 		"?," +
			 		"?," +
			 		"?)";
		try{
			 con = getSqlMapClientTemplate().getDataSource().getConnection();
			 PreparedStatement ps = con.prepareStatement(sql);
			 for (DataModel view : list) {
				ps.setString(1, view.getProduct_name());
				ps.setString(2, view.getProduct_id());
				ps.setString(3, view.getComment());
				ps.setString(4, view.getProduct_url());
				ps.setString(5, view.getMaker_name());
				ps.setString(6, view.getSmall_image_url());
				ps.setString(7, view.getBig_image_url());
				ps.setInt( 8,view.getLowest_price());
				ps.setInt(9,view.getMax_price());
				ps.setDouble(10, view.getMy_price());
				ps.setString(11, view.getShop_name());
				ps.setString(12, view.getShop_url());
				ps.setString(13, view.getSource_site_name());
				ps.setInt(14, view.getCount());
				ps.setInt(15, view.getNow_price());
				ps.setInt(16,view.getPage_total());
				ps.setInt(17,view.getTotal());
				ps.setDouble(18,view.getEvaluation());
				ps.setTimestamp(19, view.getCreate_time());
				ps.addBatch();
			}
			 ps.executeBatch();
		}catch(Exception e){
			 e.printStackTrace();
		}finally{
			 try {
				con.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}
	
	//得到所有的数据
	public List<SearchResultItemVO> getCacheModelAll() throws Exception{
		List<SearchResultItemVO> list = new ArrayList<SearchResultItemVO>();
		String sql = "select * from shop_data";
		Connection con = null;
		try{
			con = getSqlMapClientTemplate().getDataSource().getConnection();
			Statement ps = con.createStatement();
			ResultSet rs = ps.executeQuery(sql);
			while(rs.next()){
				SearchResultItemVO model = new SearchResultItemVO();
				model.setProductName(rs.getString("product_name"));
				model.setProductID(rs.getString("product_id"));
				model.setComment(rs.getString("comment"));
				model.setProductUrl(rs.getString("product_url"));
				model.setMakerName(rs.getString("maker_name"));
				model.setSmallImageUrl(rs.getString("small_image_url"));
				model.setBigImageUrl(rs.getString("big_image_url"));
				model.setLowestPrice(rs.getInt("lowest_price"));
				model.setMaxPrice(rs.getInt("max_price"));
				model.setMyPrice(rs.getDouble("my_price"));
				model.setShopName(rs.getString("shop_name"));
				model.setShopUrl(rs.getString("shop_url"));
				model.setSourceSiteName(rs.getString("source_site_name"));
				model.setCount(rs.getInt("count"));
				model.setNowPrice(rs.getInt("now_price"));
				model.setEvaluation(rs.getDouble("evaluation"));
				list.add(model);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try {
				con.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return list;
	}
}
